﻿using System;
using System.Collections.Generic;
using System.Text;
using DBList;
using DBList.Entities;
using System.Data.SqlClient;
using System.Data;

namespace DBAccessor.Accessor
{
    public class CPNAccessor
    {
        public const String const_SuperClass_News       = @"news";
        public const String const_SuperClass__Customer  = @"guest";
        public const String const_SuperClass__Product   = @"prod";
        public const String const_SuperClass__Knowledge = @"knowlg";

		public List<RoleData> GetRoleData()
        {
			List<RoleData> list = new List<RoleData>();
            SqlParameter[] sqlParameters = {
            };
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetRoleData", sqlParameters);
            while (reader.Read())
            {
				RoleData cate = new RoleData();
				cate.RoleID = int.Parse(reader["ID"].ToString());
                cate.RoleName = reader["Name"].ToString();

                list.Add(cate);
            }
            reader.Dispose();
            return list;
        }
		public List<ClassData> GetAllKind(int	operateid)
		{
			List<ClassData> list = new List<ClassData>();
			SqlParameter[] sqlParameters = {
				new SqlParameter("@operaterid",SqlDbType.Int)
			};
			sqlParameters[0].Value = operateid;

			SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetAllKind", sqlParameters);
			while (reader.Read())
			{
				ClassData cate = new ClassData();

				cate.ClassId = int.Parse(reader["ID"].ToString());
				cate.ClassName = reader["Name"].ToString();
				Decimal dmoney;
				if (Decimal.TryParse(reader["Money"].ToString(), out dmoney))
					cate.ClassMoney = dmoney;
				cate.KindCode = reader["Code"].ToString();

				list.Add(cate);
			}
			reader.Dispose();
			return list;
		}
		public List<LYDanData> GetTookDanList(int nPageIndex, int nPageSize, out int outTotal, string nameMatch, string mdmcMatch, string kindmatch)
        {
			List<LYDanData> list = new List<LYDanData>();

			outTotal = 0;
			SqlParameter[] sqlParamenter = { 
                new SqlParameter("@pageIndex",SqlDbType.Int, 4),
                new SqlParameter("@pageSize",SqlDbType.Int, 4),
                new SqlParameter("@namematch",SqlDbType.NVarChar, 20),
                new SqlParameter("@mdmcmatch",SqlDbType.NVarChar, 20),
                new SqlParameter("@kindmatch",SqlDbType.NVarChar, 20),
                new SqlParameter("@outtotalsize",SqlDbType.Int, 4)
            };
			sqlParamenter[0].Value = nPageIndex;
			sqlParamenter[1].Value = nPageSize;
            sqlParamenter[2].Value = null;
			sqlParamenter[3].Value = null;
			sqlParamenter[4].Value = null;
			sqlParamenter[5].Value = outTotal;
			sqlParamenter[5].Direction = ParameterDirection.Output;
			if (nameMatch != null && nameMatch.Length > 0)
				sqlParamenter[2].Value = nameMatch;
			if (mdmcMatch != null && mdmcMatch.Length > 0)
				sqlParamenter[3].Value = mdmcMatch;
			if (kindmatch != null && kindmatch.Length > 0)
				sqlParamenter[4].Value = kindmatch;

			SqlCommand cmd = new SqlCommand();
			SqlDataReader read = SqlHelper.ExecuteReader(cmd, "GetTookDanList", sqlParamenter);
            while (read.Read())
            {
				LYDanData info = new LYDanData();
                info.ID = int.Parse(read["ID"].ToString());
				info.KindName = read["KindName"].ToString();
				info.KindID = int.Parse(read["Kind"].ToString());
				DateTime dt;
				if (DateTime.TryParse(read["TookTime"].ToString(), out dt))
					info.TookTime = dt;
				int	nNum1 = 0, nNum2 = 0;
				int.TryParse(read["BeginNumber"].ToString(), out nNum1);
				int.TryParse(read["EndNumber"].ToString(), out nNum2);
				if (nNum1 != 0 || nNum2 != 0){
					info.TotalNumber = nNum2 - nNum1 + 1;
					info.BeginNumber = nNum1;
					info.EndNumber = nNum2;
				}
				Decimal dde;
				if (Decimal.TryParse(read["SingleMoney"].ToString(), out dde))
					info.SingleMoney = dde;
				info.Code = read["Code"].ToString();
				info.MemberID = int.Parse(read["Taker"].ToString());
				info.Introduction = read["Introduction"].ToString();
				info.Address = read["Address"].ToString();
				info.RealName = read["RealName"].ToString();

                list.Add(info);
            }
			read.Close();
			read.Dispose();
			int.TryParse(cmd.Parameters["@outTotalSize"].Value.ToString(), out outTotal);
			cmd.Dispose();
			return list;
        }

		public TakerKindData GetTakerKindInfo(int tookid)
        {
            SqlParameter[] sqlParameter = {
                 new SqlParameter("@tookid",SqlDbType.Int,4)
            };
			sqlParameter[0].Value = tookid;
			TakerKindData articleData = new TakerKindData();
			SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetTakerInfoByTookID", sqlParameter);
            if (reader.HasRows){
                reader.Read();
                articleData.MemberID = int.Parse(reader["MemberId"].ToString());
				articleData.KindID = int.Parse(reader["KindID"].ToString());
                articleData.Introduction = reader["Introduction"].ToString();
				articleData.Address = reader["Address"].ToString();
                articleData.RealName = reader["RealName"].ToString();
                articleData.KindName = reader["KindName"].ToString();
				Decimal		dMoney;
				if (Decimal.TryParse(reader["SingleMoney"].ToString(), out dMoney))
					articleData.SingleMoney = dMoney;
            }
            reader.Dispose();
            return articleData;
        }
		public LYDanData GetLYDanData(int tookid)
        {
            SqlParameter[] sqlParameter = {
                 new SqlParameter("@tookid",SqlDbType.Int,4)
            };
			sqlParameter[0].Value = tookid;

			LYDanData lydData = new LYDanData();
			SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetLYDanData", sqlParameter);
            if (reader.HasRows){
                reader.Read();

				lydData.BeginNumber = int.Parse(reader["BeginNumber"].ToString());
				lydData.EndNumber = int.Parse(reader["EndNumber"].ToString());
				if (lydData.BeginNumber != 0 || lydData.EndNumber != 0)
					lydData.TotalNumber = lydData.EndNumber - lydData.BeginNumber + 1;
				lydData.MemberID = int.Parse(reader["Taker"].ToString());
				lydData.Comment = reader["Comment"].ToString();
				lydData.KindID = int.Parse(reader["Kind"].ToString());
				DateTime dt;
				if (DateTime.TryParse(reader["TookTime"].ToString(), out dt))
					lydData.TookTime = dt;
            }
            reader.Dispose();
			return lydData;
        }

		public int GetTookListKindMaxInt(int kindid)
		{
			SqlCommand cmd = null;
			int nRet = -8;
			try {
				SqlParameter[] sqlParameter = {
					 new SqlParameter("@kindid",SqlDbType.Int,4),
	            };
				sqlParameter[0].Value = kindid;
				cmd = new SqlCommand();
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "GetTookListKindMaxInt", sqlParameter);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
			}
			catch (System.Exception e) {
				if (cmd != null)
					cmd.Parameters.Clear();
				String strErr = e.Message;
				nRet = -8;
			}
			return nRet;
		}

		public int ActivateBarCode(int nopeid, int kindid, int beginnum, int endnum, int takerid, string strcomment, int tookid)
        {
			SqlCommand cmd = null;
			int nRet = -8;
			try
			{
				SqlParameter[] sqlParameters = {
					new SqlParameter("@operaterid",SqlDbType.Int),
					new SqlParameter("@kindid",SqlDbType.Int),
					new SqlParameter("@beginnum",SqlDbType.Int),
					new SqlParameter("@endnum",SqlDbType.Int),
					new SqlParameter("@takerid",SqlDbType.Int),
					new SqlParameter("@tookcomment",SqlDbType.NVarChar),
					new SqlParameter("@tookid",SqlDbType.Int)
	            };
				sqlParameters[0].Value = nopeid;
				sqlParameters[1].Value = kindid;
				sqlParameters[2].Value = beginnum;
				sqlParameters[3].Value = endnum;
				sqlParameters[4].Value = takerid;
				sqlParameters[5].Value = strcomment;
				sqlParameters[6].Value = null;
				if (tookid != 0)
					sqlParameters[6].Value = tookid;

				cmd = new SqlCommand();
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "ActivateBarCode", sqlParameters);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
			}
			catch (System.Exception e)
			{
				if (cmd != null)
					cmd.Parameters.Clear();
				String strErr = e.Message;
				nRet = -8;
			}
			return nRet;
        }

		public int NullifyBarCode(int nopeid, int kindid, int beginnum, int endnum, string strcomment)
        {
			SqlCommand cmd = null;
			int nRet = -8;
			try
			{
				SqlParameter[] sqlParameters = {
					new SqlParameter("@operaterid",SqlDbType.Int),
					new SqlParameter("@kindid",SqlDbType.Int),
					new SqlParameter("@beginnum",SqlDbType.Int),
					new SqlParameter("@endnum",SqlDbType.Int),
					new SqlParameter("@comment",SqlDbType.NVarChar),
	            };
				sqlParameters[0].Value = nopeid;
				sqlParameters[1].Value = kindid;
				sqlParameters[2].Value = beginnum;
				sqlParameters[3].Value = endnum;
				sqlParameters[4].Value = null;
				if (strcomment != null && strcomment.Length > 0)
					sqlParameters[4].Value = strcomment;

				cmd = new SqlCommand();
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "NullifyBarCode", sqlParameters);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
			}
			catch (System.Exception e)
			{
				if (cmd != null)
					cmd.Parameters.Clear();
				String strErr = e.Message;
				nRet = -8;
			}
			return nRet;
        }

		public int GetServerCurTime(ref DateTime curdt)
		{
			int nRet = -8;
			SqlCommand cmd = null;
			try
			{
				SqlParameter[] sqlParameters = {
					new SqlParameter("@curtime",SqlDbType.DateTime, 8),
	            };
				sqlParameters[0].Value = curdt;
				sqlParameters[0].Direction = ParameterDirection.Output;
				cmd = new SqlCommand();
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "GetServerCurTime", sqlParameters);
				DateTime.TryParse(cmd.Parameters["@curtime"].Value.ToString(), out curdt);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
				cmd.Dispose();
			}
			catch (System.Exception e) {
				if (cmd != null)
					cmd.Parameters.Clear();
				String strErr = e.Message;
				nRet = -8;
			}
			return nRet;
		}
		public int IsMultiTakerExist(int kindid, int beginnum, int endnum, string strtaker)
		{
			SqlCommand cmd = null;
			int nRet = -8;
			try
			{
				SqlParameter[] sqlParameters = {
					new SqlParameter("@kindid",SqlDbType.Int),
					new SqlParameter("@beginnum",SqlDbType.Int),
					new SqlParameter("@endnum",SqlDbType.Int),
					new SqlParameter("@takerid",SqlDbType.Int),
	            };
				sqlParameters[0].Value = kindid;
				sqlParameters[1].Value = beginnum;
				sqlParameters[2].Value = endnum;
				sqlParameters[3].Value = null;
				if (strtaker != null && strtaker.Length > 0){
					sqlParameters[3].Value = int.Parse(strtaker);
				}

				cmd = new SqlCommand();
				cmd.Parameters.Add(new SqlParameter("@return", SqlDbType.Int));
				cmd.Parameters["@return"].Direction = ParameterDirection.ReturnValue;
				nRet = SqlHelper.ExecuteNonQuery(cmd, "IsMultiTakerExist", sqlParameters);
				int.TryParse(cmd.Parameters["@return"].Value.ToString(), out nRet);
			}
			catch (System.Exception e)
			{
				if (cmd != null)
					cmd.Parameters.Clear();
				String strErr = e.Message;
				nRet = -8;
			}
			return nRet;
		}
        public ClassData GetGoodsKindInfo(int classid)
        {
            SqlParameter[] sqlParameter = {
                 new SqlParameter("@classid",SqlDbType.Int,4)
            };
            sqlParameter[0].Value = classid;
            ClassData clsData = new ClassData();
            SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "GetGoodsKindInfo", sqlParameter);
            if (reader.HasRows) {
                reader.Read();

				string	str1 = string.Empty;
				Decimal dMoney = 0;
				str1 = reader["Name"].ToString();
				Decimal.TryParse(reader["Money"].ToString(), out dMoney);

				clsData.ClassId = classid;
				clsData.ClassName = str1;
				clsData.ClassMoney = dMoney;
				clsData.KindCode = reader["Code"].ToString();
            }
			reader.Close();
            reader.Dispose();
            return clsData;
        }

		public List<LJTJItemData> LiJuanTotalTJ(int nOpeID, string kindbstr)
		{
			List<LJTJItemData> list = new List<LJTJItemData>();

			SqlParameter[] sqlParamenter = { 
                new SqlParameter("@operaterid",SqlDbType.Int, 4),
                new SqlParameter("@kindbstr",SqlDbType.NVarChar, 20),
            };
			sqlParamenter[0].Value = nOpeID;
			sqlParamenter[1].Value = null;
			if (kindbstr != null && kindbstr.Length > 0)
				sqlParamenter[1].Value = kindbstr;

			int		totalnum, sentnum, usednum;
			SqlDataReader read = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "LiJuanTotalTJ", sqlParamenter);
			while (read.Read())
			{
				LJTJItemData info = new LJTJItemData();

				info.KindName = read["KindName"].ToString();
				info.KindID = int.Parse(read["kindid"].ToString());
				totalnum = sentnum = usednum = 0;
				int.TryParse(read["totalnumber"].ToString(), out totalnum);
				int.TryParse(read["sentnumber"].ToString(), out sentnum);
				int.TryParse(read["usednumber"].ToString(), out usednum);
				info.TotalNumber = totalnum;
				info.UsedNumber = usednum;
				info.SentNumber = sentnum;
				Decimal dde;
				if (Decimal.TryParse(read["SingleMoney"].ToString(), out dde))
				{
					info.SingleMoney = dde;
					info.TotalMoney = dde * totalnum;
					info.SentMoney = dde * sentnum;
					info.UsedMoney = dde * usednum;
				}

				list.Add(info);
			}
			read.Close();
			read.Dispose();

			LJTJItemData adItem = new LJTJItemData();
			adItem.KindID = 0;
			adItem.KindName = @"总计";
			foreach (LJTJItemData item in list){
				adItem.TotalNumber += item.TotalNumber;
				adItem.SentNumber += item.SentNumber;
				adItem.UsedNumber += item.UsedNumber;

				adItem.TotalMoney += item.TotalMoney;
				adItem.SentMoney += item.SentMoney;
				adItem.UsedMoney += item.UsedMoney;
			}
			list.Add(adItem);
			return list;
		}

		public List<LJTJItemData> LJTotalTJDetail(int kindid, int nPageIndex, int nPageSize, out int outTotal, string mdmatch)
		{
			List<LJTJItemData> list = new List<LJTJItemData>();

			outTotal = 0;
			SqlParameter[] sqlParamenter = { 
                new SqlParameter("@kindid",SqlDbType.Int, 4),
                new SqlParameter("@pageIndex",SqlDbType.Int, 4),
                new SqlParameter("@pageSize",SqlDbType.Int, 4),
                new SqlParameter("@outTotalSize",SqlDbType.Int, 4),
                new SqlParameter("@mdmatch",SqlDbType.NVarChar, 20),
            };
			sqlParamenter[0].Value = kindid;
			sqlParamenter[1].Value = nPageIndex;
			sqlParamenter[2].Value = nPageSize;
			sqlParamenter[3].Value = outTotal;
			sqlParamenter[4].Value = null;
			if (mdmatch != null && mdmatch.Length > 0)
				sqlParamenter[4].Value = mdmatch;
			sqlParamenter[3].Direction = ParameterDirection.Output;

			SqlCommand cmd = new SqlCommand();
			SqlDataReader read = SqlHelper.ExecuteReader(cmd, "LJTotalTJDetail", sqlParamenter);
			while (read.Read())
			{
				LJTJItemData info = new LJTJItemData();

				info.KindName = read["KindName"].ToString();
				info.KindID = kindid;
				int totalnum = 0, usednum = 0;
				int.TryParse(read["totalnumber"].ToString(), out totalnum);
				int.TryParse(read["usednumber"].ToString(), out usednum);
				info.TotalNumber = totalnum;
				info.UsedNumber = usednum;
				Decimal dde;
				if (Decimal.TryParse(read["SingleMoney"].ToString(), out dde))
				{
					info.SingleMoney = dde;
					info.TotalMoney = dde * totalnum;
					info.UsedMoney = dde * usednum;
				}

				info.Introduction = read["Introduction"].ToString();
				info.Address = read["Address"].ToString();
				info.RealName = read["RealName"].ToString();

				list.Add(info);
			}
			read.Close();
			read.Dispose();
			int.TryParse(cmd.Parameters["@outTotalSize"].Value.ToString(), out outTotal);
			cmd.Dispose();
			return list;
		}
	}
}
